﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public DataSet QueryGSDAT(string art, string var)
        {
            DataSet setReturn = new DataSet();
            DataSet SResult = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM GSDAT WHERE  "
            + "  GSMANDANT=:GSMANDANT AND GSART=:GSART AND GSVAR=:GSVAR";
            or1.Parameters.Add("GSMANDANT", OracleType.NVarChar).Value = "00";
            or1.Parameters.Add("GSART", OracleType.NVarChar).Value = art;
            or1.Parameters.Add("GSVAR", OracleType.NVarChar).Value = var;
            SResult = Connect2Proxy(or1);
            if (SResult.Tables.Count > 0)
            {
                tbLocal = SResult.Tables[0];
                tbLocal.TableName = "GSDAT";
            }
            setReturn.Merge(SResult);
            return setReturn;
        }
        public DataSet QueryGSDAT(DataRow dwData, int option = 1)
        {
            DataSet setReturn = new DataSet();
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            OracleCommand or1;
            // GSCMANDANT GSCART GSCVAR GSCFARBE


            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM GSDAT WHERE  ROWNUM<2 ";
                    break;
                case 1:
                    or1.CommandText = "SELECT * FROM GSDAT WHERE  "
                    + "  GSMANDANT=:GSMANDANT AND GSART=:GSART AND GSVAR=:GSVAR";
                    or1.Parameters.Add("GSMANDANT", OracleType.NVarChar).Value = dwData["GSMANDANT"].ToString();
                    or1.Parameters.Add("GSART", OracleType.NVarChar).Value = dwData["GSART"].ToString();
                    or1.Parameters.Add("GSVAR", OracleType.NVarChar).Value = dwData["GSVAR"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT DISTINCT GS.* FROM EKPOS GA LEFT JOIN GSDAT GS ON EKPMANDANT=GSMANDANT   AND utl_raw.cast_to_varchar2(EKPART)=GSART"
                        + "  AND utl_raw.cast_to_varchar2(EKPVAR)=GSVAR"
                        + "  WHERE EKPSA='PO' AND EKPARTGR=1 AND EKPMANDANT=:EKPMANDANT AND  EKPAUFKRS=:EKPAUFKRS AND EKPAUFLFD=:EKPAUFLFD ";
                    or1.Parameters.Add("EKPMANDANT", OracleType.NVarChar).Value = dwData["EKPMANDANT"].ToString();
                    or1.Parameters.Add("EKPAUFKRS", OracleType.NVarChar).Value = dwData["EKPAUFKRS"].ToString();
                    or1.Parameters.Add("EKPAUFLFD", OracleType.NVarChar).Value = dwData["EKPAUFLFD"].ToString();
                    break;
                case 4:
                    or1.CommandText = "  SELECT DISTINCT GS.* FROM GAAUF GA RIGHT JOIN GSDAT GS ON GAMANDANT=GSMANDANT"
                        + " AND GAART=GSART "
                        + "  WHERE GAMANDANT=:GAMANDANT AND  GAAUFKRS=:GAAUFKRS AND GAAUFLFD=:GAAUFLFD ";
                    or1.Parameters.Add("GAMANDANT", OracleType.NVarChar).Value = dwData["GAMANDANT"].ToString();
                    or1.Parameters.Add("GAAUFKRS", OracleType.NVarChar).Value = dwData["GAAUFKRS"].ToString();
                    or1.Parameters.Add("GAAUFLFD", OracleType.NVarChar).Value = dwData["GAAUFLFD"].ToString();
                    break;
                case 5:
                    or1.CommandText = "  SELECT DISTINCT GSCMANDANT AS GSMANDANT,GSCART AS GSART,GSCVAR AS GSVAR"
                    + " FROM GSCOLOR WHERE GSCART NOT IN (SELECT GSART FROM GSDAT WHERE GSMANDANT=:GSMANDANT ) ";
                    or1.Parameters.Add("GSMANDANT", OracleType.NVarChar).Value = dwData["GSMANDANT"].ToString();                 
                    break;
                case 6:
                    or1.CommandText = "  SELECT GAAUFKRS,GAAUFLFD,GAAUFPOS,M.* FROM VKPOS JOIN GAAUF ON VKPSA='  ' AND VKPMANDANT=GAMANDANT AND VKPAUFKRS=GAAUFKRS AND VKPAUFLFD=GAAUFLFD AND VKPAUFPOS=GAAUFPOS AND GASA=610"
                    + " JOIN GSDAT M ON  GSART=GAART AND GSVAR=GAVAR "
                        +"WHERE VKPMANDANT=:VKPMANDANT AND VKPAUFKRS=:VKPAUFKRS AND VKPAUFLFD=:VKPAUFLFD ";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
                    or1.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwData["VKPAUFLFD"].ToString();
                    break;
                case 7:
                    or1.CommandText = "  SELECT GAAUFKRS,GAAUFLFD,GAAUFPOS,M.* FROM VKPOS JOIN GAAUF ON VKPSA='  ' AND VKPMANDANT=GAMANDANT AND VKPAUFKRS=GAAUFKRS AND VKPAUFLFD=GAAUFLFD AND VKPAUFPOS=GAAUFPOS AND GASA=610"
                    + " JOIN GSDAT M ON  GSART=GAART AND GSVAR=GAVAR "
                        + "WHERE VKPMANDANT=:VKPMANDANT AND VKPAUFKRS=:VKPAUFKRS AND VKPAUFLFD=:VKPAUFLFD AND VKPAUFPOS=:VKPAUFPOS ";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    or1.Parameters.Add("VKPAUFKRS", OracleType.NVarChar).Value = dwData["VKPAUFKRS"].ToString();
                    or1.Parameters.Add("VKPAUFLFD", OracleType.NVarChar).Value = dwData["VKPAUFLFD"].ToString();
                    or1.Parameters.Add("VKPAUFPOS", OracleType.NVarChar).Value = dwData["VKPAUFPOS"].ToString();
                    break; 
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                tbLocal.TableName = "GSDAT";
            }
            setReturn.Merge(Result);
            return setReturn;
        }
    }
}
